﻿using System;
using System.Collections.Generic;
using System.Text;
using BarCode.Module;
using BarCode.Common;
using BarCode.Bean;
using System.Data.OleDb;
using System.Data;

namespace BarCode.AccessDao
{
    public class TmpOutRoomMdbDao
    {
        private static readonly log4net.ILog LogUtil = log4net.LogManager.GetLogger(typeof(TmpOutRoomMdbDao));

        private AccessHelper tmpOutRoomMdb;
        public TmpOutRoomMdbDao()
        {
            tmpOutRoomMdb = new AccessHelper(Consts.MDB_TMP_OUTROOM);
        }

        public void Release()
        {
            tmpOutRoomMdb.Release();
        }

        /// <summary>
        /// 删除车辆牌照信息
        /// </summary>
        /// <returns></returns>
        public int deleteLicenceInfo()
        {
            return tmpOutRoomMdb.ExecuteSql("delete from tbllicence");
        }

        /// <summary>
        /// 插入车辆牌照信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertLicence(List<LicenceInfo> lstLicence)
        {
            string insertSql = "insert into [tbllicence] ([packID_start], [packID_end], [licence], [isout], [delbz], [operator], [opdt]) Values (@packID_start, @packID_end, @licence, @isout, @delbz, @operator, @opdt)";
            int retCnt = 0;
            foreach (LicenceInfo tmpItem in lstLicence)
            {
                OleDbParameter[] sqlParams = new OleDbParameter[7];
                sqlParams[0] = new OleDbParameter("packID_start", tmpItem.packID_start);
                sqlParams[1] = new OleDbParameter("packID_end", tmpItem.packID_end);
                sqlParams[2] = new OleDbParameter("licence", tmpItem.licence);
                sqlParams[3] = new OleDbParameter("isout", tmpItem.isout);
                sqlParams[4] = new OleDbParameter("delbz", tmpItem.delbz);
                sqlParams[5] = new OleDbParameter("operator", tmpItem.operatorName);
                sqlParams[6] = new OleDbParameter("opdt", tmpItem.opdt);
                retCnt += tmpOutRoomMdb.ExecuteSql(insertSql, sqlParams);
            }
            return retCnt;
        }

        /// <summary>
        /// 删除待水密封箱表信息
        /// </summary>
        /// <returns></returns>
        public int deleteMnoprnPreInfo()
        {
            return tmpOutRoomMdb.ExecuteSql("delete from tblmnoprn_pre");
        }

        /// <summary>
        /// 插入待水密封箱表信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertMnoprnPre(List<MnoprnPreInfo> lstMnoprnPre)
        {
            string insertSql = "insert into [tblmnoprn_pre] ([listID],[orderID],[meters_code],[meters_directions],[meters_IDstart],[meters_IDend],[packID_start],[packID_end],[meters_tolmax],[meters_tolmin],[customer_code],[meters_ploss],[istest],[ispack],[delbz],[operator],[opdt],[note],[packcount],[codecount])"
                            + "Values (@listID,@orderID,@meters_code,@meters_directions,@meters_IDstart,@meters_IDend,@packID_start,@packID_end,@meters_tolmax,@meters_tolmin,@customer_code,@meters_ploss,@istest,@ispack,@delbz,@operator,@opdt,@note,@packcount,@codecount)";
            int retCnt = 0;
            foreach (MnoprnPreInfo tmpItem in lstMnoprnPre)
            {
                OleDbParameter[] sqlParams = new OleDbParameter[20];
                sqlParams[0] = new OleDbParameter("listID", tmpItem.listID);
                sqlParams[1] = new OleDbParameter("orderID", tmpItem.orderID);
                sqlParams[2] = new OleDbParameter("meters_code", tmpItem.meters_code);
                sqlParams[3] = new OleDbParameter("meters_directions", tmpItem.meters_directions);
                sqlParams[4] = new OleDbParameter("meters_IDstart", tmpItem.meters_IDstart);
                sqlParams[5] = new OleDbParameter("meters_IDend", tmpItem.meters_IDend);
                sqlParams[6] = new OleDbParameter("packID_start", tmpItem.packID_start);
                sqlParams[7] = new OleDbParameter("packID_end", tmpItem.packID_end);
                sqlParams[8] = new OleDbParameter("meters_tolmax", tmpItem.meters_tolmax);
                sqlParams[9] = new OleDbParameter("meters_tolmin", tmpItem.meters_tolmin);
                sqlParams[10] = new OleDbParameter("customer_code", tmpItem.customer_code);
                sqlParams[11] = new OleDbParameter("meters_ploss", tmpItem.meters_ploss);
                sqlParams[12] = new OleDbParameter("istest", tmpItem.istest);
                sqlParams[13] = new OleDbParameter("ispack", tmpItem.ispack);
                sqlParams[14] = new OleDbParameter("delbz", tmpItem.delbz);
                sqlParams[15] = new OleDbParameter("operator", tmpItem.operatorName);
                sqlParams[16] = new OleDbParameter("opdt", tmpItem.opdt);
                sqlParams[17] = new OleDbParameter("note", "".Equals(tmpItem.note) ? null : tmpItem.note);
                sqlParams[18] = new OleDbParameter("packcount", tmpItem.packcount);
                sqlParams[19] = new OleDbParameter("codecount", tmpItem.codecount);
                //sqlParams[20] = new OleDbParameter("program", tmpItem.program);
                retCnt += tmpOutRoomMdb.ExecuteSql(insertSql, sqlParams);
            }
            return retCnt;
        }

        /// <summary>
        /// 删除待终检密封箱表信息
        /// </summary>
        /// <returns></returns>
        public int deleteZjmfPreInfo()
        {
            return tmpOutRoomMdb.ExecuteSql("delete from tblzjmf_pre");
        }

        /// <summary>
        /// 插入待终检密封箱表信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertZjmfPre(List<ZjmfPreInfo> lstZjmfPre)
        {
            string insertSql = "insert into [tblzjmf_pre] ([listID],[meters_code],[meters_directions],[meters_IDstart],[meters_IDend],[customer_code],[ismf],[delbz],[operator],[opdt],[note])"
                            + "Values (@listID,@meters_code,@meters_directions,@meters_IDstart,@meters_IDend,@customer_code,@ismf,@delbz,@operator,@opdt,@note)";
            int retCnt = 0;
            foreach (ZjmfPreInfo tmpItem in lstZjmfPre)
            {
                OleDbParameter[] sqlParams = new OleDbParameter[11];
                sqlParams[0] = new OleDbParameter("listID", tmpItem.listID);
                sqlParams[1] = new OleDbParameter("meters_code", tmpItem.meters_code);
                sqlParams[2] = new OleDbParameter("meters_directions", tmpItem.meters_directions);
                sqlParams[3] = new OleDbParameter("meters_IDstart", tmpItem.meters_IDstart);
                sqlParams[4] = new OleDbParameter("meters_IDend", tmpItem.meters_IDend);
                sqlParams[5] = new OleDbParameter("customer_code", tmpItem.customer_code);
                sqlParams[6] = new OleDbParameter("ismf", tmpItem.ismf);
                sqlParams[7] = new OleDbParameter("delbz", tmpItem.delbz);
                sqlParams[8] = new OleDbParameter("operator", tmpItem.operatorName);
                sqlParams[9] = new OleDbParameter("opdt", tmpItem.opdt);
                sqlParams[10] = new OleDbParameter("note", "".Equals(tmpItem.note) ? null : tmpItem.note);  
                retCnt += tmpOutRoomMdb.ExecuteSql(insertSql, sqlParams);
            }
            return retCnt;
        }

        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <returns></returns>
        public int deleteCustomerInfo()
        {
            return tmpOutRoomMdb.ExecuteSql("delete from tblcustomer");
        }

        /// <summary>
        /// 插入客户信息
        /// </summary>
        /// <param name="lstLicence"></param>
        /// <returns></returns>
        public int insertCustomer(List<CustomerInfo> lstCustomer)
        {
            string insertSql = "insert into [tblcustomer] ([customer_code],[customer_name],[opdt],[delbz],[note])"
                            + "Values (@customer_code,@customer_name,@opdt,@delbz,@note)";
            int retCnt = 0;
            foreach (CustomerInfo tmpItem in lstCustomer)
            {
                OleDbParameter[] sqlParams = new OleDbParameter[5];
                sqlParams[0] = new OleDbParameter("customer_code,", tmpItem.customer_code);
                sqlParams[1] = new OleDbParameter("customer_name,", "".Equals(tmpItem.customer_name) ? null : tmpItem.customer_name);
                sqlParams[2] = new OleDbParameter("opdt,", tmpItem.opdt);
                sqlParams[3] = new OleDbParameter("delbz,", tmpItem.delbz);
                sqlParams[4] = new OleDbParameter("note", "".Equals(tmpItem.note) ? null : tmpItem.note); 
                retCnt += tmpOutRoomMdb.ExecuteSql(insertSql, sqlParams);
            }
            return retCnt;
        }


        /// <summary>
        /// 通过查询获得当前所有已测、未封并且未做清除标志的待封信息
        /// </summary>
        /// <returns></returns>
        public DataTable selectUnPackList()
        {
            // 2019-03-14 逆光微信Txt文件  读取批次条件把‘istest=已测’去掉
            //String selectSql = "select p.*, c.customer_name from tblmnoprn_pre p left join tblcustomer c on p.customer_code = c.customer_code where p.ispack='未封' and p.istest='已测' and p.delbz='0'";
            String selectSql = "select p.*, c.customer_name from tblmnoprn_pre p left join tblcustomer c on p.customer_code = c.customer_code where p.ispack='未封' and p.delbz='0' order by p.opdt desc";
            DataSet retSet = tmpOutRoomMdb.Query(selectSql);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                return retSet.Tables[0];
            } else {
                return null;
            }

        }

        /// <summary>
        /// 通过查询获得当前所有已测、未封并且未做清除标志的待封信息
        /// </summary>
        /// <returns></returns>
        public DataTable selectCustomerNames()
        {
            String selectSql = "select customer_name from tblcustomer";
            DataSet retSet = tmpOutRoomMdb.Query(selectSql);
            if (retSet != null && retSet.Tables.Count > 0)
            {
                return retSet.Tables[0];
            }
            else
            {
                return null;
            }

        }

    }
}
